package com.example.tkserver.dao;

import com.example.tkserver.entity.Question;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface QuestionDao {

    @Select("SELECT " +
            "u.avatarUrl,u.nickName,unix_timestamp(q.questionTime) datetime,q.*" +
            "FROM " +
            "user u,questions q " +
            "WHERE u.uid = q.uid AND q.qid = #{qid}")
    public Question getQuestion(int qid);


    @Select("SELECT " +
            "u.avatarUrl,u.nickName,unix_timestamp(q.questionTime) datetime,q.*" +
            "FROM " +
            "user u,questions q " +
            "WHERE u.uid = q.uid " +
            "ORDER BY RAND() LIMIT 1")
    public Question randomQuestion();

    @Select("SELECT COUNT(qid) FROM questions")
    public int getQuestionsNum();

    @Select("SELECT " +
            "u.avatarUrl,u.nickName,unix_timestamp(q.questionTime) datetime,q.*" +
            "FROM " +
            "user u,questions q " +
            "WHERE u.uid = q.uid " +
            "ORDER BY q.questionTime DESC " +
            "LIMIT #{start},#{end}")
    public List<Question> getNewQuestionsBetween(int start, int end);

    @Select("SELECT COUNT(qid) " +
            "FROM " +
            "questions " +
            "WHERE answerNum > #{hotAnswerNum}")
    public int getHotQuestionsNum(int hotAnswerNum);

    @Select("SELECT " +
            "u.avatarUrl,u.nickName,unix_timestamp(q.questionTime) datetime,q.*" +
            "FROM " +
            "user u,questions q " +
            "WHERE u.uid = q.uid AND answerNum >= #{hotAnswerNum} " +
            "LIMIT #{start},#{end}")
    public List<Question> getHotQuestionsBetween(int hotAnswerNum,int start, int end);


    @Select("SELECT COUNT(qid) FROM questions WHERE answerNum = 0")
    public int getWaitQuestionNum();

    @Select("SELECT " +
            "u.avatarUrl,u.nickName,unix_timestamp(q.questionTime) datetime,q.*" +
            "FROM " +
            "user u,questions q " +
            "WHERE u.uid = q.uid AND answerNum = 0 " +
            "LIMIT #{start},#{end}")
    public List<Question> getWaitQuestionsBetween(int start, int end);

    @Insert("INSERT INTO " +
            "questions(uid,title,content,type,award,questionTime) " +
            "VALUES(#{uid},#{title},#{content},#{type},#{award},FROM_UNIXTIME(#{datetime},'%Y-%m-%d %h:%i:%s'))")
    public int pushQuestion(Question question);
}
